﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;
namespace DAO
{
    public class TinhTrangVeDAO
    {
        public int CapNhatSoGheTrong(TinhTrangVeDTO soghetrong, string i, string k)
        {
            string sql = " Update TinhTrangVe set SoGheTrong = SoGheTrong -1";
            sql += " where MaChuyenBay= '" + i + "'" + " AND";
            sql += " MaHangVe = '" + k + "'";
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            int res = -1;
            res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }
        public int CapNhatSoGheDat(TinhTrangVeDTO soghedat, string i, string k)
        {
            string sql = " Update TinhTrangVe set SoGheDat = SoGheDat +1";
            sql += " where MaChuyenBay= '" + i + "'" + " AND";
            sql += " MaHangVe = '" + k + "'";
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            int res = -1;
            res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }
        public TinhTrangVeDTO loadsoghe(string i, string k)
        {

           /* String sql = "SELECT ttv.SoGheTrong ";
            sql += "from TinhTrangVe ttv,HangVe hv ,ChuyenBay cb ";
            sql += "where ttv.MaChuyenBay='" + i + "'" + " AND ";
            sql += "ttv.MaHangVe='" + k + "'" + " AND ";
            sql += "hv.MaHangVe = ttv.MaHangVe" + " AND ";
            sql += "ttv.MaChuyenBay = cb.MaChuyenBay";*/
            String sql = "SELECT SoGheTrong from TinhTrangVe where MaChuyenBay='" + i + "' and MaHangVe='" + k + "'";
            //sql += "where MaChuyenBay='" + i + "'";
           // sql += "and MaHangVe='" + k + "'";
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            TinhTrangVeDTO ttv = new TinhTrangVeDTO();
            if (reader != null)
            {
                while (reader.Read())
                {
                    ttv.SoGheTrong = reader.GetInt16(0);

                }
            }
            conn.Close();
            return ttv;



        }

        #region Ngo Minh Nhat - Source code

        public int ThemTinhTrangVe(TinhTrangVeDTO ttvdto)
        {
            String sql = string.Format("insert into TinhTrangVe values ('{0}','{1}','{2}','{3}') ",ttvdto.MaCB,ttvdto.MaHangVe,ttvdto.SoGheTrong,ttvdto.SoGheDat);
            //insert into TinhTrangVe values ('CB01','HV01','49','0')
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            int res = -1;
            res = cmd.ExecuteNonQuery();
            return res;
        }

        #endregion
    }
}
